package com.starinside.wp.repository;

import com.starinside.wp.domain.ChildActivity;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;


public interface ChildActivityRepository
        extends JpaRepository<ChildActivity, String>, JpaSpecificationExecutor<ChildActivity>
{


    @Query(value = "SELECT ca.title, ca.classIds, GROUP_CONCAT(c.name SEPARATOR ', ') as classNames, " +
            "ca.type, ca.address, ca.startTime, ca.endTime, ca.deadlineTime, ca.content, ca.remark, " +
            "ca.areaId, ca.sourceType, ca.createTime , ca.id" +
            "FROM child_activity ca " +
            "LEFT JOIN classes c ON FIND_IN_SET(c.id, ca.classIds) > 0 " +
            "WHERE (:areaId IS NULL OR ca.areaId = :areaId) " +
            "GROUP BY ca.id", nativeQuery = true)
    List<Object[]> findByQueryNew(@Param("areaId") String areaId);

    @Query(value = "SELECT COUNT(DISTINCT ca.id) FROM child_activity ca " +
            "LEFT JOIN classes c ON FIND_IN_SET(c.id, ca.classIds) > 0 " +
            "WHERE (:areaId IS NULL OR ca.areaId = :areaId)", nativeQuery = true)
    Long findCountByQueryNew(@Param("areaId") String areaId);


}
